package com.marktotop.demo;

import javax.swing.*;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.DefaultTableModel;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;

/**
 * @author WXLD
 * @data 2021/4/28 - 21:48
 */
public class WindowDemo implements ActionListener {

    public static JFrame jFrame;
    public static JDialog dialog;
    public static JPanel topPanel, topLeft, topRight, centerPanel,centerLeftPanel,centerRightPanel, bottomPanel,footerPanel,dialogPanel1,dialogPanel2,dialogPanel3,dialogPanel4;
    public static JTextField stdInput,axisInput, axisIT,holeInput,holeIT;
    public static Object[][] stdHeaderRows = new Object[3][22];
    public static Object[][] axisHeaderRows = new Object[4][19];
    public static Object[][] axisHeaderRowsDown = new Object[4][17];
    public static Object[][] holeHeaderRows = new Object[3][23];
    public static Object[][] holeHeaderRowsDown = new Object[3][22];
    public static JButton topLeftBtn,topRightBtn,centerBtn,showFaceRough;

    public WindowDemo() {
        jFrame = new JFrame("机械公差与配合查询系统");
        UIManager.put("Label.font",new Font("微软雅黑", Font.PLAIN, 18));
        UIManager.put("TextField.font", new java.awt.Font("微软雅黑", Font.PLAIN, 18));
        UIManager.put("Button.font", new Font("宋体",Font.PLAIN, 18));
        jFrame.setBounds(450, 100, 1100, 900);
        //  整体布局 3行1列
        jFrame.setLayout(new GridLayout(4, 1, 0, 10));

        // 底部table标准偏差表头 stdAction
        stdHeaderRows[0] = new Object[]{"公称尺寸/mm",ComplexTable.mergeCellX,"标准公差等级",ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX};
        stdHeaderRows[1] = new Object[]{ComplexTable.mergeCellY,ComplexTable.mergeCellY,"IT01","IT0","IT1","IT2","IT3","IT4","IT5","IT6","IT7","IT8","IT9","IT10","IT11","IT12","IT13","IT14","IT15","IT16","IT17","IT18"};
        stdHeaderRows[2] = new Object[]{"大于","至","μm",ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,"mm",ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX};


        // 底部table-轴的基本偏差表头 axisAction 上半部分
        axisHeaderRows[0] = new Object[]{"公称尺寸/mm",ComplexTable.mergeCellX,"基本偏差数值",ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX};
        axisHeaderRows[1] = new Object[]{ComplexTable.mergeCellY,ComplexTable.mergeCellY,"上极限偏差 es",ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,"下极限偏差 ei",ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,"js结果"};
        axisHeaderRows[2] = new Object[]{ComplexTable.mergeCellY,ComplexTable.mergeCellY,"a","b","c","cd","d","e","ef","f","fg","g","h","j",ComplexTable.mergeCellX,ComplexTable.mergeCellX,"k",ComplexTable.mergeCellX,ComplexTable.mergeCellY};
        axisHeaderRows[3] = new Object[]{"大于","至","所有标准公差等级",ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,"IT5、IT6","IT7","IT8","IT4~IT7","<=IT3或>IT7",ComplexTable.mergeCellY};


        // 底部table-轴的基本偏差表头 axisAction 下半部分
        axisHeaderRowsDown[0] = new Object[]{"公称尺寸/mm",ComplexTable.mergeCellX,"基本偏差数值",ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX};
        axisHeaderRowsDown[1] = new Object[]{ComplexTable.mergeCellY,ComplexTable.mergeCellY,"下极限偏差 ei",ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX};
        axisHeaderRowsDown[2] = new Object[]{ComplexTable.mergeCellY,ComplexTable.mergeCellY,"m","n","p","r","s","t","u","v","x","y","z","za","zb","zc"};
        axisHeaderRowsDown[3] = new Object[]{"大于","至","所有标准公差等级",ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX};


        // 底部table-孔的基本偏差表头 holeAction 上半部分
        holeHeaderRows[0] = new Object[]{"公称尺寸/mm",ComplexTable.mergeCellX,"下极限偏差 EI",ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,"上极限偏差 ES",ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,"JS结果"};
        holeHeaderRows[1] = new Object[]{ComplexTable.mergeCellY,ComplexTable.mergeCellY,"A","B","C","CD","D","E","EF","F","FG","G","H","J",ComplexTable.mergeCellX,ComplexTable.mergeCellX,"K",ComplexTable.mergeCellX,"M",ComplexTable.mergeCellX,"N",ComplexTable.mergeCellX,ComplexTable.mergeCellY};
        holeHeaderRows[2] = new Object[]{"大于","至","所有标准公差等级",ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,"IT6","IT7","IT8","<=IT8",">IT8","<=IT8",">IT8","<=IT8",">IT8",ComplexTable.mergeCellY};


        // 底部table-孔的基本偏差表头 holeAction 下半部分
        holeHeaderRowsDown[0] = new Object[]{"公称尺寸/mm",ComplexTable.mergeCellX,"上极限偏差 ES",ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,"∆",ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX};
        holeHeaderRowsDown[1] = new Object[]{ComplexTable.mergeCellY,ComplexTable.mergeCellY,"P~ZC","P","R","S","T","U","V","X","Y","Z","ZA","ZB","ZC",ComplexTable.mergeCellY,ComplexTable.mergeCellY,ComplexTable.mergeCellY,ComplexTable.mergeCellY,ComplexTable.mergeCellY,ComplexTable.mergeCellY};
        holeHeaderRowsDown[2] = new Object[]{"大于","至","<=IT7",">IT7",ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,ComplexTable.mergeCellX,"IT3","IT4","IT5","IT6","IT7","IT8"};


        //  顶部左右分隔
        topPanel = new JPanel(new GridLayout(1, 2, 10, 0));
        topLeft = new JPanel(new FlowLayout(FlowLayout.CENTER,10,45));
        topRight = new JPanel(new FlowLayout(FlowLayout.CENTER,10,18));
        centerPanel = new JPanel(new GridLayout(1,2,10,10));
        centerLeftPanel = new JPanel(new FlowLayout(FlowLayout.CENTER,10,18));
        centerRightPanel = new JPanel(null);
        bottomPanel = new JPanel(new BorderLayout());
        footerPanel = new JPanel(new BorderLayout());



        jFrame.add(topPanel);
        stdInput = new JTextField();
        stdInput.setPreferredSize(new Dimension(280, 35));

        JLabel label1 = new JLabel("公称尺寸值为:");
        topLeft.add(label1);
        topLeft.add(stdInput);


        topLeftBtn = new JButton("点击查询");
        topLeftBtn.setLayout(null);
        topLeftBtn.setPreferredSize(new Dimension(200, 50));
        topLeftBtn.addActionListener(this);
        topLeft.add(topLeftBtn);
        topLeft.setBorder(BorderFactory.createTitledBorder("标准公差查询"));


        // 右上
        topRight.setBorder(BorderFactory.createTitledBorder("轴的基本偏差数值查询"));

        axisInput = new JTextField();
        axisInput.setPreferredSize(new Dimension(280, 35));
        JLabel label3 = new JLabel("公称尺寸值为:");
        topRight.add(label3);
        topRight.add(axisInput);

        axisIT = new JTextField();
        axisIT.setPreferredSize(new Dimension(280, 35));
        JLabel label5 = new JLabel("输入IT 精度号:");
        topRight.add(label5);
        axisIT.setText("--例如:IT5");
        topRight.add(axisIT);

        topRightBtn = new JButton("点击查询轴偏差");
        topRightBtn.setLayout(null);
        topRightBtn.setPreferredSize(new Dimension(200, 50));
        topRightBtn.addActionListener(this);
        topRight.add(topRightBtn);

        topPanel.add(topLeft);
        topPanel.add(topRight);


        // 中间
        // 左边
        centerLeftPanel.setBorder(BorderFactory.createTitledBorder("孔的基本偏差数值查询"));
        centerRightPanel.setBorder(BorderFactory.createTitledBorder("表面粗糙度查询"));


        holeInput = new JTextField();
        holeInput.setPreferredSize(new Dimension(280, 35));
        JLabel label6 = new JLabel("公称尺寸值为:");
        centerLeftPanel.add(label6);
        centerLeftPanel.add(holeInput);


        holeIT = new JTextField();
        holeIT.setPreferredSize(new Dimension(280, 35));
        JLabel label8 = new JLabel("输入IT 精度号:");
        centerLeftPanel.add(label8);
        holeIT.setText("--例如:IT5");
        centerLeftPanel.add(holeIT);

        centerBtn = new JButton("点击查询孔偏差");
        centerBtn.setLayout(null);
        centerBtn.setPreferredSize(new Dimension(200, 50));
        centerLeftPanel.add(centerBtn);

        jFrame.add(centerPanel);
        centerPanel.add(centerLeftPanel);
        centerPanel.add(centerRightPanel);

        centerBtn.addActionListener(this);


        //中间 右边
        JLabel messageLabel = new JLabel("输出中 * 代表: 在大于IT7级的相应数值上增加一个∆值。");
        messageLabel.setBounds(50,70,450,30);
        centerRightPanel.add(messageLabel);

        showFaceRough = new JButton("表面粗糙度展示");
        showFaceRough.setBounds(330,150,200,50);
        centerRightPanel.add(showFaceRough);

        showFaceRough.addActionListener(this);


        // 底部输出
        bottomPanel.setBorder(BorderFactory.createTitledBorder("输出结果 1"));
        jFrame.add(bottomPanel);

        footerPanel.setBorder(BorderFactory.createTitledBorder("输出结果 2"));
        jFrame.add(footerPanel);

        jFrame.setResizable(false);
        jFrame.setVisible(true);
        jFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    }

    public static void main(String[] args) {
        new WindowDemo();
    }

    @Override
    public void actionPerformed(ActionEvent e) {
        if (e.getSource() == topLeftBtn){
            //  表格数据
            Object[][] rowData=new Object[1][22];

            // 将底部 bottomPanel 和 footerPanel 置空
            panelSetNull();

            String stdInputValue = stdInput.getText();
            if(stdInputValue.equals("")||stdInputValue.contains("-")){
                // 失败
                JOptionPane.showMessageDialog(jFrame,"输入数据有误，尝试重新输入！");
                return;
            }
            // 成功
            Connection conn = JDBCService.getConn();
            String sql = "select * from std_tolerance where "+stdInputValue+" <= smax ORDER BY smax LIMIT 1";
            try {
                PreparedStatement statement = conn.prepareStatement(sql);
                ResultSet resultSet = statement.executeQuery();
                if(resultSet.next()){
                    // 结果
                    int count=0;
                    do {
                        rowData[count][0] = resultSet.getString("smin");
                        rowData[count][1] = resultSet.getString("smax");
                        rowData[count][2] = resultSet.getString("IT01");
                        rowData[count][3] = resultSet.getString("IT0");
                        rowData[count][4] = resultSet.getString("IT1");
                        rowData[count][5] = resultSet.getString("IT2");
                        rowData[count][6] = resultSet.getString("IT3");
                        rowData[count][7] = resultSet.getString("IT4");
                        rowData[count][8] = resultSet.getString("IT5");
                        rowData[count][9] = resultSet.getString("IT6");
                        rowData[count][10] = resultSet.getString("IT7");
                        rowData[count][11] = resultSet.getString("IT8");
                        rowData[count][12] = resultSet.getString("IT9");
                        rowData[count][13] = resultSet.getString("IT10");
                        rowData[count][14] = resultSet.getString("IT11");
                        rowData[count][15] = resultSet.getString("IT12");
                        rowData[count][16] = resultSet.getString("IT13");
                        rowData[count][17] = resultSet.getString("IT14");
                        rowData[count][18] = resultSet.getString("IT15");
                        rowData[count][19] = resultSet.getString("IT16");
                        rowData[count][20] = resultSet.getString("IT17");
                        rowData[count][21] = resultSet.getString("IT18");

                        count++;
                    }while(resultSet.next());
                }else{
                    System.out.println("未查到结果！");
                    JOptionPane.showMessageDialog(jFrame,"未找到结果！");
                    return;
                }
                JDBCService.release(resultSet,statement,conn);
            } catch (Exception ex) {
                return;
            }
             // 数据存放在RowData中,绘制并刷新
            bottomPanel.add(new JScrollPane(new ComplexTable(stdHeaderRows,rowData)));
            bottomPanel.revalidate();//重绘
            // 将底部 panel 置空
            footerPanel.removeAll();
            footerPanel.repaint();
            footerPanel.validate();
            // 将输入框置空
            stdInput.setText("");
        } else if (e.getSource() == topRightBtn){
            /**
             * 轴偏差思路：通过查出的int整型值拼接”+“来达到显示”+XXX“的效果
             */

            // 将底部 bottomPanel 和 footerPanel 置空
            panelSetNull();

            //  表格数据
            Object[][] axisData=new Object[1][19];
            Object[][] axisDataDown=new Object[1][16];

            String axisInputValue = axisInput.getText();
            String axisITInput = axisIT.getText();
            if (!axisITInput.contains("IT")){
                JOptionPane.showMessageDialog(jFrame,"输入数据有误，输入误差值时应包含IT字符,例：IT2");
                axisIT.setText("--例如:IT5");
                return;
            }
            if(axisInputValue.equals("")||axisInputValue.contains("-")||axisITInput.equals("")||axisITInput.contains("-")){
                // 失败
                JOptionPane.showMessageDialog(jFrame,"输入数据有误，尝试重新输入！");
                return;
            }
            Connection conn = JDBCService.getConn();
            String stdSql = "select "+axisITInput+" from std_tolerance where "+axisInputValue+" <= smax ORDER BY smax LIMIT 1";
            try {
                PreparedStatement statement = conn.prepareStatement(stdSql);
                ResultSet resultSet = statement.executeQuery();
                if(resultSet.next()) {
                    Double axis_value = Double.valueOf(resultSet.getString(axisITInput));
                    int axis_result = Integer.parseInt(resultSet.getString(axisITInput));
                    // 截取
                    int axis_sub = Integer.parseInt(axisITInput.substring(2));
                    if( axis_sub>=7 && axis_sub<=11){

                        if (axis_result %2 == 0){
                            axis_result = axis_result >> 1 ;
                        }else {
                            axis_result = (axis_result -1) >> 1;
                        }
                    }else {
                        axis_value = axis_value / 2;
                    }
                    String axisSql = "select * from axis_tolerance where "+axisInputValue+" <= smax ORDER BY smax LIMIT 1";
                    PreparedStatement statement2 = conn.prepareStatement(axisSql);
                    ResultSet resultSet2 = statement2.executeQuery();
                    if (resultSet2.next()) {
                        // 结果
                        int count = 0;
                        do {
                            axisData[count][0] = resultSet2.getString("smin");
                            axisData[count][1] = resultSet2.getString("smax");
                            axisData[count][2] = resultSet2.getString("a");
                            axisData[count][3] = resultSet2.getString("b");
                            axisData[count][4] = resultSet2.getString("c");
                            axisData[count][5] = resultSet2.getString("cd");
                            axisData[count][6] = resultSet2.getString("d");
                            axisData[count][7] = resultSet2.getString("e");
                            axisData[count][8] = resultSet2.getString("ef");
                            axisData[count][9] = resultSet2.getString("f");
                            axisData[count][10] = resultSet2.getString("fg");
                            axisData[count][11] = resultSet2.getString("g");
                            axisData[count][12] = resultSet2.getString("h");
                            axisData[count][13] = resultSet2.getString("IT5a6");
                            axisData[count][14] = resultSet2.getString("IT7");
                            axisData[count][15] = resultSet2.getString("IT8");
                            axisData[count][16] = resultSet2.getString("IT4b7");
                            axisData[count][17] = resultSet2.getString("IT3e7");
                            axisData[count][18] = "±"+(axis_sub>=7 && axis_sub<=11 ? axis_result : axis_value);

                            axisDataDown[count][0] = resultSet2.getString("smin");
                            axisDataDown[count][1] = resultSet2.getString("smax");
                            axisDataDown[count][2] = resultSet2.getString("m");
                            axisDataDown[count][3] = resultSet2.getString("n");
                            axisDataDown[count][4] = resultSet2.getString("p");
                            axisDataDown[count][5] = resultSet2.getString("r");
                            axisDataDown[count][6] = resultSet2.getString("s");
                            axisDataDown[count][7] = resultSet2.getString("t");
                            axisDataDown[count][8] = resultSet2.getString("u");
                            axisDataDown[count][9] = resultSet2.getString("v");
                            axisDataDown[count][10] = resultSet2.getString("x");
                            axisDataDown[count][11] = resultSet2.getString("y");
                            axisDataDown[count][12] = resultSet2.getString("z");
                            axisDataDown[count][13] = resultSet2.getString("za");
                            axisDataDown[count][14] = resultSet2.getString("zb");
                            axisDataDown[count][15] = resultSet2.getString("zc");


                            count++;
                        } while (resultSet2.next());
                        // 数据存放在RowData中,绘制并刷新
                        bottomPanel.add(new JScrollPane(new ComplexTable(axisHeaderRows,axisData)));
                        bottomPanel.revalidate();//重绘
                        // 下半部分数据存放在RowData中,绘制并刷新
                        footerPanel.add(new JScrollPane(new ComplexTable(axisHeaderRowsDown,axisDataDown)));
                        footerPanel.revalidate();//重绘
                        axisInput.setText("");
                        axisIT.setText("--例如:IT5");
                    } else {
                        JOptionPane.showMessageDialog(jFrame, "轴偏差中未找到结果！公称尺寸应小于等于500.");
                        return;
                    }
                }else {
                    JOptionPane.showMessageDialog(jFrame, "标准偏差中未找到结果,请查验输入数据！");
                    return;
                }
                JDBCService.release(resultSet,statement,conn);
            } catch (SQLException ex) {
                JOptionPane.showMessageDialog(WindowDemo.jFrame,"未找到结果，请验证数据后重试！");
            }
        }else if (e.getSource() == centerBtn){
            /**
             * 孔偏差思路：通过查出的String字符值直接显示”+XXX“的效果
             */
            //  表格数据
            Object[][] holeData = new Object[1][23];
            Object[][] holeDataDown = new Object[1][21];

            // 将底部 bottomPanel 和 footerPanel 置空
            panelSetNull();

            String holeInputValue = holeInput.getText();
            String holeITInput = holeIT.getText();
            if (!holeITInput.contains("IT")){
                JOptionPane.showMessageDialog(jFrame,"输入数据有误，输入误差值时应包含IT字符,例：IT2");
                holeIT.setText("--例如:IT5");
                return;
            }
            if(holeInputValue.equals("")||holeInputValue.contains("-")||holeITInput.equals("")||holeITInput.contains("-")){
                // 失败
                JOptionPane.showMessageDialog(jFrame,"输入数据有误，尝试重新输入！");
                return;
            }
            String stdSql = "select "+holeITInput+" from std_tolerance where "+holeInputValue+ " <= smax ORDER BY smax LIMIT 1";
            ResultSet stdResult = getStdResult(stdSql);
            try {
                if (stdResult.next()){
                    Connection conn = JDBCService.getConn();
                    // 拿到std中对应的偏差值
                    Double hole_value = Double.valueOf(stdResult.getString(holeITInput));
                    int hole_result = Integer.parseInt(stdResult.getString(holeITInput));
                    // 截取
                    int hole_sub = Integer.parseInt(holeITInput.substring(2));
                    if( hole_sub>=7 && hole_sub<=11){
                        if (hole_result %2 == 0){
                            hole_result = hole_result >> 1 ;
                        }else {
                            hole_result = (hole_result -1) >> 1;
                        }
                    }else {
                        hole_value = hole_value / 2;
                    }
                    // 查询 hole偏差表
                    String holeSql = "select * from hole_tolerance where "+holeInputValue+" <= smax ORDER BY smax LIMIT 1";
                    PreparedStatement holeStatement = conn.prepareStatement(holeSql);
                    ResultSet holeResultSet = holeStatement.executeQuery();
                    if (holeResultSet.next()) {
                        // 结果
                        int count = 0;
                        do {
                            holeData[count][0] = holeResultSet.getString("smin");
                            holeData[count][1] = holeResultSet.getString("smax");
                            holeData[count][2] = holeResultSet.getString("A");
                            holeData[count][3] = holeResultSet.getString("B");
                            holeData[count][4] = holeResultSet.getString("C");
                            holeData[count][5] = holeResultSet.getString("CD");
                            holeData[count][6] = holeResultSet.getString("D");
                            holeData[count][7] = holeResultSet.getString("E");
                            holeData[count][8] = holeResultSet.getString("EF");
                            holeData[count][9] = holeResultSet.getString("F");
                            holeData[count][10] = holeResultSet.getString("FG");
                            holeData[count][11] = holeResultSet.getString("G");
                            holeData[count][12] = holeResultSet.getString("H");
                            holeData[count][13] = holeResultSet.getString("IT6");
                            holeData[count][14] = holeResultSet.getString("IT7");
                            holeData[count][15] = holeResultSet.getString("IT8");
                            holeData[count][16] = holeResultSet.getString("MinIT8K");
                            holeData[count][17] = holeResultSet.getString("MaxIT8K");
                            holeData[count][18] = holeResultSet.getString("MinIT8M");
                            holeData[count][19] = holeResultSet.getString("MaxIT8M");
                            holeData[count][20] = holeResultSet.getString("MinIT8N");
                            holeData[count][21] = holeResultSet.getString("MaxIT8N");
                            holeData[count][22] = "±"+(hole_sub>=7 && hole_sub<=11 ? hole_result : hole_value);

                            holeDataDown[count][0] = holeResultSet.getString("smin");
                            holeDataDown[count][1] = holeResultSet.getString("smax");
                            holeDataDown[count][2] = holeResultSet.getString("PbZC");
                            holeDataDown[count][3] = holeResultSet.getString("P");
                            holeDataDown[count][4] = holeResultSet.getString("R");
                            holeDataDown[count][5] = holeResultSet.getString("S");
                            holeDataDown[count][6] = holeResultSet.getString("T");
                            holeDataDown[count][7] = holeResultSet.getString("U");
                            holeDataDown[count][8] = holeResultSet.getString("V");
                            holeDataDown[count][9] = holeResultSet.getString("X");
                            holeDataDown[count][10] = holeResultSet.getString("Y");
                            holeDataDown[count][11] = holeResultSet.getString("Z");
                            holeDataDown[count][12] = holeResultSet.getString("ZA");
                            holeDataDown[count][13] = holeResultSet.getString("ZB");
                            holeDataDown[count][14] = holeResultSet.getString("ZC");
                            holeDataDown[count][15] = holeResultSet.getString("ZIT3");
                            holeDataDown[count][16] = holeResultSet.getString("ZIT4");
                            holeDataDown[count][17] = holeResultSet.getString("ZIT5");
                            holeDataDown[count][18] = holeResultSet.getString("ZIT6");
                            holeDataDown[count][19] = holeResultSet.getString("ZIT7");
                            holeDataDown[count][20] = holeResultSet.getString("ZIT8");

                            count++;
                        } while (holeResultSet.next());
                        // 数据存放在 holeData 中,绘制并刷新
                        bottomPanel.add(new JScrollPane(new ComplexTable(holeHeaderRows,holeData)));
                        bottomPanel.revalidate();//重绘
                        // 数据存放在 holeDataDown 中,绘制并刷新
                        footerPanel.add(new JScrollPane(new ComplexTable(holeHeaderRowsDown,holeDataDown)));
                        footerPanel.revalidate();//重绘
                        holeInput.setText("");
                        holeIT.setText("--例如:IT5");
                    } else {
                        JOptionPane.showMessageDialog(jFrame, "孔偏差中未找到结果！公称尺寸应小于等于500.");
                        return;
                    }
                    JDBCService.release(holeResultSet,holeStatement,conn);
                }else {
                    JOptionPane.showMessageDialog(jFrame, "标准偏差中未找到结果,请查验输入数据！");
                    return;
                }
            } catch (SQLException ex) {
                JOptionPane.showMessageDialog(WindowDemo.jFrame,"未找到结果，请验证数据后重试！");
            }
        }else if (e.getSource() == showFaceRough){
            faceRoughDialog();
            // 将底部 bottomPanel 和 footerPanel 置空
            panelSetNull();
        }
    }

    /**
     * 设置表面粗糙度对话框
     */
    public static void faceRoughDialog(){
        dialog = new JDialog(jFrame,"表面粗糙度参数值展示");
        dialog.setResizable(false);
        dialog.setSize(600,590);
        dialog.setLayout(new GridLayout(4,1,10,10));

        dialogPanel1 = new JPanel(new FlowLayout(FlowLayout.CENTER,200,10));
        dialogPanel2 = new JPanel(new FlowLayout(FlowLayout.CENTER,200,10));
        dialogPanel3 = new JPanel(new FlowLayout(FlowLayout.CENTER,200,10));
        dialogPanel4 = new JPanel(new FlowLayout(FlowLayout.CENTER,200,10));

        //  Ra参数表
        JLabel labelRa = new JLabel("Ra的参数值 ( 摘自GB/T 1031-2009 )  ( 单位：μm )");
        Object[] rcColumns={"0.012","0.2","3.2","50"};
        Object[][] rcData={{0.025,0.4,6.3,100},{0.05,0.8,12.5,null},{0.1,1.6,2.5,null}};
        DefaultTableModel rcModel = new DefaultTableModel(rcData, rcColumns);
        JTable table1 = new JTable(rcModel);
        // table 文字居中
        DefaultTableCellRenderer tcr = new DefaultTableCellRenderer();
        tcr.setHorizontalAlignment(SwingConstants.CENTER);
        table1.setDefaultRenderer(Object.class, tcr);
        table1.setFont(new Font("Menu.font", Font.PLAIN, 15));
        table1.setRowHeight(22);

        dialogPanel1.add(labelRa);
        dialogPanel1.add(new JScrollPane(table1));

        //  Rz参数表
        JLabel labelRz = new JLabel("Rz的参数值 ( 摘自GB/T 1031-2009 )  ( 单位：μm )");
        Object[] rzColumns={"0.025","0.4","6.3","100","1600"};
        Object[][] rzData={{0.05,0.8,12.5,200,null},{0.1,1.6,25,400,null},{0.2,3.2,50,800,null}};
        DefaultTableModel rzModel = new DefaultTableModel(rzData, rzColumns);
        JTable table2 = new JTable(rzModel);
        table2.setDefaultRenderer(Object.class, tcr);
        table2.setFont(new Font("Menu.font", Font.PLAIN, 15));
        table2.setRowHeight(22);

        dialogPanel2.add(labelRz);
        dialogPanel2.add(new JScrollPane(table2));

        //  Rsm参数表
        JLabel labelRsm = new JLabel("Rsm的参数值 ( 摘自GB/T 1031-2009 )  ( 单位：mm )");
        Object[] rsmColumns={"0.006","0.05","0.4","3.2"};
        Object[][] rsmData={{0.0125,0.1,0.8,6.3},{0.025,0.2,1.6,12.5}};
        DefaultTableModel rsmModel = new DefaultTableModel(rsmData, rsmColumns);
        JTable table3 = new JTable(rsmModel);
        table3.setDefaultRenderer(Object.class, tcr);
        table3.setFont(new Font("Menu.font", Font.PLAIN, 15));
        table3.setRowHeight(33);

        dialogPanel3.add(labelRsm);
        dialogPanel3.add(new JScrollPane(table3));

        //  Rsm参数表
        JLabel labelRmr = new JLabel("Rsr(c)的参数值(%) ( 摘自GB/T 1031-2009 ) ");
        Object[] rmrColumns={"10","25","50","80"};
        Object[][] rmrData={{15,30,60,90},{20,40,70,null}};
        DefaultTableModel rmrModel = new DefaultTableModel(rmrData, rmrColumns);
        JTable table4 = new JTable(rmrModel);
        table4.setDefaultRenderer(Object.class, tcr);
        table4.setFont(new Font("Menu.font", Font.PLAIN, 15));
        table4.setRowHeight(33);

        dialogPanel4.add(labelRmr);
        dialogPanel4.add(new JScrollPane(table4));

        dialog.add(dialogPanel1);
        dialog.add(dialogPanel2);
        dialog.add(dialogPanel3);
        dialog.add(dialogPanel4);

        dialog.setVisible(true);
        dialog.setLocation(680,260);
        dialog.setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE);
    }

    /**
     * 从标准偏差中查询结果
     * @param sql
     * @return
     */
    public static ResultSet getStdResult(String sql){
        Connection conn = JDBCService.getConn();
        try {
            PreparedStatement statement = conn.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            return resultSet;
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(WindowDemo.jFrame,"未找到结果，请验证数据后重试！");
            return null;
        }
    }

    /**
     * 做刷新操作
     * 将 bottomPanel 和 footerPanel 置空
     */
    public static void panelSetNull(){
        // 将底部 bottomPanel 置空
        bottomPanel.removeAll();
        bottomPanel.repaint();
        bottomPanel.validate();

        // 将底部 footerPanel 置空
        footerPanel.removeAll();
        footerPanel.repaint();
        footerPanel.validate();
    }
}
